package org.banaan.sql

import nl.mayisal.metadata.{DatabaseStructure,TableStructure,TableRelation};
import nl.mayisal.metadata.factories.{ColumnStructureFactory};
import nl.mayisal.metadata.columns.{AbstractColumnStructure};

import java.util.{Properties};
import java.sql.{DriverManager,ResultSet};
import scala.collection.mutable.{ListBuffer};

class SQLConnection(val url:String, val info:Properties) {
	val connection = DriverManager.getConnection(url,info);
	var connectionIsOpen = true;

	def getCatalogName() : String = {
		connection.getCatalog();
	}

	def getDriverDescription() : String = {
		val metaData = connection.getMetaData();
		metaData.getDriverName() + " " + metaData.getDriverVersion() + " ("+metaData.getDriverMajorVersion()+"."+metaData.getDriverMinorVersion()+")\n";
	}

	def getDatabaseDescription() : String = {
		val metaData = connection.getMetaData();
		metaData.getDatabaseProductName() + " " + metaData.getDatabaseProductVersion() + " ("+ metaData.getDatabaseMajorVersion() +"."+metaData.getDatabaseMinorVersion()+")\n";
	}

	def getAvailableTableTypes() : List[String] = {
		val tableTypes = new ListBuffer[String]();
		val rs = this.connection.getMetaData().getTableTypes();
		while (rs.next()) {
			tableTypes += rs.getString(1);
		}
		rs.close();
		tableTypes.toList;
	}

	def getTableNames(tableType : String) : List[String] = {
		val tableNames = new ListBuffer[String]();
		val ttypes = new Array[String](1);
		ttypes(0) = tableType;
		val rs = this.connection.getMetaData().getTables(getCatalogName(), null,null, ttypes);
		while (rs.next()) {
			tableNames += rs.getString(3);
		}
		rs.close();
		tableNames.toList;
	}

	def getColumns(table : String) : List[AbstractColumnStructure[_]] = {
		val columnNames = new ListBuffer[AbstractColumnStructure[_]]();
		val rs = this.connection.getMetaData().getColumns(getCatalogName(), null, table, null);
		while (rs.next()) {
			val column = 
				ColumnStructureFactory.createColumn(
						rs.getString(4),	// COLUMN_NAME String => column name 
						rs.getInt(5),		// DATA_TYPE int => SQL type from java.sql.Types 
						rs.getInt(7),		// COLUMN_SIZE int => column size. 
						rs.getInt(9),		// DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
						rs.getInt(10),		// NUM_PREC_RADIX int => Radix (typically either 10 or 2)
						rs.getString(13)	// COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null) 
				);
			if (rs.getInt(11) == 1) {
				column.setNullable(true);
			}
			columnNames += column;
		}
		rs.close();
		columnNames.toList;
	}

	def getRelations(database: DatabaseStructure, table: TableStructure) : List[TableRelation] = {
		val relations = new ListBuffer[TableRelation]();
		val rs = this.connection.getMetaData().getCrossReference(getCatalogName(), null, table.getName(), null, null, null);
		while (rs.next()) {
			val parentColumn = table.getColumn(rs.getString(4));
			val foreignTable = database.getTable(rs.getString(7));
			val foreignColumn = foreignTable.getColumn(rs.getString(8));

			relations += new TableRelation(table,parentColumn,foreignTable,foreignColumn);
		}
		relations.toList;
	}

	def close() {
		connectionIsOpen = false;
		connection.close();
	}

	override def finalize {
		if(connectionIsOpen)
			close();
		super.finalize()
	}

}
